namespace :statistic do

  task timetable: :environment do
    current_day = Date.parse("2018-06-05")
    timetable = Timetable.joins(:company, :store, :coach, :course).select(
        :id,
        'companies.name as company_name',
        'stores.name as store_name',
        'coaches.name as coach_name',
        'courses.name as course_name',
        "CASE
           WHEN courses.en_name=courses.name then courses.name
           ELSE CONCAT(courses.en_name, '|', courses.name)
         END as course_name",
        :class_start_time,
        :price,
        :member_price,
        :place,
        :appointed_member,
    ).where(class_start_time: current_day.yesterday..current_day)
    timetable.each {|t|
      timetable_statistic = TimetableStatistic.new
      timetable_statistic.timetable_id = t.id
      timetable_statistic.company = t.company_name
      timetable_statistic.store = t.store_name
      timetable_statistic.coach = t.coach_name
      timetable_statistic.course = t.course_name
      timetable_statistic.class_start_time = t.class_start_time
      timetable_statistic.price = t.price
      timetable_statistic.member_price = t.member_price
      timetable_statistic.place = t.place.to_i
      timetable_statistic.appointed = t.appointed_member.size
      timetable_statistic.signed = t.appointments.signed.count
      timetable_statistic.channel_appointed = Order.joins(:appointment).where(source: '渠道', status: :paid, appointments: {timetable_id: t.id}).sum("appointments.quantity")
      timetable_statistic.save
    }
  end

  task course: :environment do
    current_day = Date.today
    Company.all.each {|company|
      timetable = Timetable.select(
          "CASE
             WHEN courses.en_name=courses.name then courses.name
             ELSE CONCAT(courses.en_name, '|', courses.name)
           END as course_name",
          'SUM(place) as place_sum',
          'SUM(array_length(queues,1)) as queue_sum',
          'SUM(array_length(appointed_member,1)) as appointed_sum'
      ).joins(:course).where(
          class_start_time: current_day.yesterday..current_day,
          company_id: company.id
      ).group("course_name")
      timetable.each {|t|
        course_statistic = CourseStatistic.new
        course_statistic.company = company.name
        course_statistic.course = t.course_name
        course_statistic.place = t.place_sum.to_i
        course_statistic.queue = t.queue_sum.to_i
        course_statistic.appointed = t.appointed_sum.to_i
        course_statistic.date = current_day.yesterday
        course_statistic.save
      }
    }
  end

  task overview: :environment do
    report_date = Date.today.prev_month
    Company.all.each {|company|
      timetables = Timetable.where(
          company_id: company.id,
          status: [:release, :signin, :overdue, :absent],
          class_start_time: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days)
      )
      #门店
      all_stores = Store.where(company_id: company.id).count
      free_stores = timetables.where(price: 0).pluck(:store_id).uniq.size
      tuition_stores = timetables.where('price > 0').pluck(:store_id).uniq.size
      #课程
      all_course = timetables.count
      all_place = timetables.sum("place") + timetables.sum("array_length(appointed_member,1)")

      free_course = timetables.where(price: 0).count
      free_place = timetables.where(price: 0).sum("place") + timetables.where(price: 0).sum("array_length(appointed_member,1)")

      tuition_course = all_course - free_course
      tuition_place = all_place - free_place
      #预约
      all_appointed = Appointment.joins(:order).where(
          orders: {company_id: company.id},
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days),
          status: [:appointed, :absent, :signed]
      ).count
      free_appointed = Order.where(
          company_id: company.id,
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days),
          status: :paid
      ).where("total = 0").count
      tuition_appointed = Order.where(
          company_id: company.id,
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days),
          status: :paid
      ).where("total > 0").count
      #订单流水
      credit = Order.where(
          company_id: company.id,
          status: [:paid, :refunded],
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days)
      ).sum(:total)
      pay_credit = Transaction.joins(:order).where(
          orders: {company_id: company.id},
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days)
      ).sum(:price)
      actually_credit = Transaction.joins(:order).where(
          orders: {company_id: company.id},
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days),
          status: :pending
      ).sum(:price)
      pay_member = Order.where(
          company_id: company.id,
          status: :paid
      ).where('total > 0').distinct(:member_id).pluck(:member_id).size
      #记录
      Overview.create(
          report_date: report_date.strftime('%Y-%m'),
          company: company.name,
          all_stores: all_stores,
          free_stores: free_stores,
          tuition_stores: tuition_stores,
          all_course: all_course,
          all_place: all_place,
          free_course: free_course,
          free_place: free_place,
          tuition_course: tuition_course,
          tuition_place: tuition_place,
          all_appointed: all_appointed,
          free_appointed: free_appointed,
          tuition_appointed: tuition_appointed,
          credit: credit,
          pay_credit: pay_credit,
          actually_credit: actually_credit,
          pay_member: pay_member
      )
    }
  end

  task re_purchase_overviews: :environment do
    report_date = Date.today.prev_month
    Company.all.each {|company|
      #当月购买用户
      purchaser = Order.find_by_sql(
          "select member_id from (
select member_id,min(created_at) as purchased_time from orders where company_id=#{company.id} and total > 0 and status in (1,2) group by member_id
) as t where t.purchased_time between '#{report_date.at_beginning_of_month}'::TIMESTAMP and '#{report_date.at_end_of_month + 1.days}'::TIMESTAMP"
      ).pluck(:member_id)
      RePurchaseOverview.create(
          report_date: report_date.strftime('%Y-%m'),
          company: company.name,
          purchaser: purchaser.size
      )
      #更新之前数据
      prev_report_date = report_date
      (1..5).each {|m|
        prev_report_date = prev_report_date.prev_month
        purchase = RePurchaseOverview.find_by(company: company.name, report_date: prev_report_date.strftime('%Y-%m'))
        if purchase.present?
          prev_purchaser = Order.find_by_sql(
              "select member_id from (
select member_id,min(created_at) as purchased_time from orders where company_id=#{company.id} and total > 0 and status in (1,2) group by member_id
) as t where t.purchased_time between '#{prev_report_date.at_beginning_of_month}'::TIMESTAMP and '#{prev_report_date.at_end_of_month + 1.days}'::TIMESTAMP"
          ).pluck(:member_id)
          re_purchaser = Order.joins(:member).where(
              company_id: company.id,
              status: [:paid, :refunded],
              created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days),
              member_id: prev_purchaser
          ).where("total > 0").pluck(:member_id).uniq.size
          purchase.send("month_#{m}=", re_purchaser)
          purchase.save
        end
      }
    }
  end

  #转化率
  task conversion: :environment do
    report_date = Date.today.prev_month
    Company.all.each {|company|
      tuition_orders = Order.where(
          status: [:paid, :refunded],
          company_id: company.id,
          created_at: report_date.at_beginning_of_month..(report_date.at_end_of_month + 1.days)
      ).where('total > ?', 0)

      members = tuition_orders.pluck(:member_id).uniq.size
      stores = tuition_orders.pluck(:store_id).uniq.size
      if stores > 0
        avg = members.to_f / stores
      else
        avg = 0
      end
      Conversion.create(
          report_date: report_date.strftime("%Y-%m"),
          company: company.name,
          avg: avg
      )
    }
  end

  #预约概况
  task appointed_overview: :environment do

  end


  task company_overview: :environment do
    Company.all.each {|company|

    }
  end
end